# coding=utf-8
from common.sqlOperate import *
from common.writeTxt import *
import os
from common.writeExcel import *
import datetime
path = os.path.dirname(__file__)  #获取文件的绝对路径  os.path.abspath(__file__)返回的是.py文件的绝对路径（完整路径）

def dealData(ob,id,name):
    num, devices = ob.reqHelmetVlt(id)  # num是违规安全帽个数  devices是具体的安全帽id
    #actual_t是围栏结束时间减去围栏开始时间；update_t是怕围栏设置过长中途点冻结如果是这种情况就要按照这个计算
    start_time,invalid_time,update_time,actual_t,update_t = ob.ruleTime(id)
    arr = []
    list_total = []
    rule_name =[name,f'违规个数：{num}',f'start:{start_time}',f'end:{invalid_time}',f'update:{update_time}',f'围栏正常持续时长：{actual_t}',f'围栏被修改后的持续时长：{update_t}'] #围栏名称和违规个数
    list_total.append(rule_name)
    com_list = ['id','离线次数','最大持续时长（s）','最大的离线时间','离线总时长']  #基础数据
    list_total.append(com_list)
    arr.append("围栏名称：" + name + "\n")
    arr.append("所有违规的安全帽总数：" + str(num) + "\n")  # 获取违规安全帽的数量

    #print(type(actual_t))

    actual_t = actual_t/2   # 为了避免误差差值减去60s作为参考值计算  改成已一半作为参考值
    update_t = update_t/2  # 为了避免误差差值减去60s作为参考值计算


    update_n = 0  # 用于计稳定数量的个数
    actual_n = 0
    #print(devices)
    for deviceid in devices:
        arr.append(deviceid[0] + " ")  # 获取对应的安全帽id
    arr.append("\n\n")
    invalid_id = []
    for deviceid in devices:
        list_single = []
        time_arr = []  # 统计两次违规时长
        time_arr2 = []  # 统计持续时间
        result = ob.reqVltDetails2(deviceid[0], id)
        # l = str(len(result))
        previous_row = None  # 定义一个上一行数据
        arr.append(deviceid[0] + '\n')
        # arr1 = []
        for res in result:
            if previous_row is not None:
                previous_value = previous_row[2]
                current_value = res[1]
                value = current_value - previous_value  # 得到时间差表示  下一次违规的开始  减去上一次违规的结束时间
                # print(value)
            else:
                value = datetime.timedelta(seconds=0)  # 默认值给一个datatime 避免获取最大值时报type错误
                value_time = datetime.timedelta(seconds=4)  # 4s内的数据不算入违规总数
                #print(value_time)

            previous_row = res  # 将这行数据赋值，当作下一次的前一行数据
            arr.append(res[1].strftime("%Y-%m-%d %H:%M:%S") + " ")
            arr.append(res[2].strftime("%Y-%m-%d %H:%M:%S") + " ")  # 将datetime转成string
            arr.append(str(res[3]) + " ")  #持续时间
            arr.append(str(value))  #离线时长
            arr.append('\n')

            time_arr2.append(res[3])  #持续时间


            # print(value)
            if value > value_time: #如果离线时长大于目前的阈值 就记录离线次数
                time_arr.append(value) #离线时长

        # print(time_arr)
        l = len(time_arr) #离线次数
        max_time2 = max(time_arr2) #最大的持续时长
        #print(max_time2)
        if l > 0:  # 求出最大的离线时间
            #max_time = max(time_arr)
            max_time = str(max(time_arr))
        else:
           max_time = '00:00:00'

        # 获取总的离线时长
        total_time = datetime.timedelta(seconds=0)

        for t in time_arr:
            total_time += t
        #print(total_time)

        # print(max_time)
        arr.append(deviceid[0] + '离线次数：' + str(
            l) + "  最大持续时长：" + str(max_time2) + "s" + "  最大的离线时间: " + max_time + " 离线总时长: " + str(
            total_time) + "\n")
        # arr.append("   "+"最大的离线时间: "+maxTime+"\n")
        # for i in arr1:
        #     arr.append(i)
        arr.append("\n")


        if l==0 and max_time2 ==0:#如果离线次数是0 并且最大持续时长也是0  认为是无效数据
            invalid_id.append(deviceid[0]) #统计无效数据
            print(deviceid[0]+'为无效数据')
        else:
            list_single.append(deviceid[0])
            list_single.append(str(l))
            list_single.append(max_time2)
            list_single.append(max_time)  # 最大离线时间
            list_single.append(str(total_time))  # 离线总时长
            list_total.append(list_single)



        if l==0 and int(max_time2)>int(actual_t) : #如果离线次数是0，并且最大持续时长是大于t 就认为是稳定
            actual_n = actual_n+1

        if l==0 and int(max_time2)>int(update_t) :
            update_n = update_n+1

    invalid_len = len(invalid_id)


    return arr,list_total,actual_n,update_n,invalid_len
def writeTxt1(ob,id, name):  # 将获取到的sql数据 整合后写入list再写入txt
    num, devices = ob.reqHelmetVlt(id)  # num是违规安全帽个数  devices是具体的安全帽id
    arr = []
    arr.append("围栏名称：" + name + "\n")
    arr.append("所有违规的安全帽总数：" + str(num) + "\n")  # 获取违规安全帽的数量
    print(devices)
    for deviceid in devices:
        arr.append(deviceid[0] + " ")  # 获取对应的安全帽id
    arr.append("\n\n")

    for deviceid in devices:
        time_arr = []  # 统计两次违规时长
        time_arr2 = []  # 统计持续时间
        result = ob.reqVltDetails2(deviceid[0], id)
        # l = str(len(result))
        previous_row = None  # 定义一个上一行数据
        arr.append(deviceid[0] + '\n')
        # arr1 = []
        for res in result:
            if previous_row is not None:
                previous_value = previous_row[2]
                current_value = res[1]
                value = current_value - previous_value  # 得到时间差表示  下一次违规的开始  减去上一次违规的结束时间
                # print(value)
            else:
                value = datetime.timedelta(seconds=0)  # 默认值给一个datatime 避免获取最大值时报type错误
                value_time = datetime.timedelta(seconds=4)  # 4s内的数据不算入违规总数
                print(value_time)

            previous_row = res  # 将这行数据赋值，当作下一次的前一行数据
            arr.append(res[1].strftime("%Y-%m-%d %H:%M:%S") + " ")
            arr.append(res[2].strftime("%Y-%m-%d %H:%M:%S") + " ")  # 将datetime转成string
            arr.append(str(res[3]) + " ")
            arr.append(str(value))
            arr.append('\n')

            time_arr2.append(res[3]) #持续时长
            #print(value)
            if value > value_time:
                time_arr.append(value) #离线时长

        # print(time_arr)
        l = len(time_arr) #离线个数
        max_time2 = str(max(time_arr2))
        if l > 0:  #求出最大的离线时间
            max_time = str(max(time_arr))
        else:
            max_time = '00:00:00'

        #获取总的离线时长
        total_time = datetime.timedelta(seconds=0)
        for t in time_arr:
            total_time += t
        print(total_time)


        # print(max_time)
        arr.append(deviceid[0] + '离线次数数：' + str(
            l) + "  最大持续时长：" + max_time2 + "s" + "  最大的离线时间: " + max_time + " 离线总时长: "+str(total_time) + "\n")
        # arr.append("   "+"最大的离线时间: "+maxTime+"\n")
        # for i in arr1:
        #     arr.append(i)
        arr.append("\n")

    t = time.strftime('%Y%m%d %H-%M-%S', time.localtime())
    # print(t)
    file_name = name + '(' + str(t) + ').txt'
    # print(file_name)

    current_date = datetime.datetime.now().strftime('%Y-%m-%d')  # 获取当前日期并格式化为文件夹名称格式，例如 '2024-03-12'
    folder_name = f'{path}/report/{current_date}_folder'

    # 创建文件夹
    os.makedirs(folder_name, exist_ok=True)  #如果存在就不重新创建

    file_path = os.path.join(folder_name, file_name)
    with open(file_path, 'w') as f:
        f.writelines(arr)  # 写入list，创建文件


def reqMaxValue(list_total):
    offline_time = []
    alloffline_time = []
    deviceid = []
    for data in list_total[2:-1]:
        offline_time.append(datetime.datetime.strptime(data[3],"%H:%M:%S"))
        alloffline_time.append(datetime.datetime.strptime(data[4],"%H:%M:%S"))
        deviceid.append(data[0])

    max_offline = max(offline_time)
    index_max_offline = offline_time.index(max_offline)
    id_max_offline = deviceid[index_max_offline]
    max_alloffline = max(alloffline_time)
    index_max_alloffline = alloffline_time.index(max_alloffline)
    id_max_alloffline = deviceid[index_max_alloffline]
    max_offline = max_offline.strftime("%H:%M:%S")
    max_alloffline = max_alloffline.strftime("%H:%M:%S")
    print(max_offline,id_max_offline, max_alloffline,id_max_alloffline)
    return max_offline,id_max_offline, max_alloffline,id_max_alloffline




if __name__ == '__main__':
    host = '192.168.3.44'
    #host = 'localhost'
    port = 3306
    #user = 'root'
    #password = 'root1234'
    user = 'test123'
    password = 'test1234'
    #db='gwza0204'
    db = 'gwza0422'
    #db = 'uwb44'
    #db = 'gwza_refactory_local'
    name = "5_10第三次围栏40"

    ob = sqlOperate(host, port, user, password, db)
    ob.sqlConnect()
    # ob.reqVltNum()
    # 获取违规信息统计
    id = ob.reqRuleId(name)
    #id = 712
    arr,list_total,actual_n,update_n,invalid_len = dealData(ob,id,name)
    max_offline,id_max_offline, max_alloffline,id_max_alloffline = reqMaxValue(list_total)
    wt = writeTxt()
    wt.write_to_txt(name,arr)
    filename = 'result.xlsx'
    we = writeExcel(filename)
    ws = we.create_excel_sheet()
    list_total.append([f'结束和开始算出稳定数:{actual_n}','','',f'更新时间算出稳定数:{update_n}'])#输入一行空数据
    list_total.append([f'最大离线时间:{max_offline}—{id_max_offline}','','',f'最大离线总时长:{max_alloffline}—{id_max_alloffline}'])
    list_total.append([f'无效设备数：{invalid_len}'])
    we.append_to_excel(list_total,ws)
    we.save_to_excel()



    #print(arr)
    #writeTxt(ob, id, name)
    # 修改安全帽类型
    # ob.changeDeviceType()

    # 获取一段时间内 总在线人数的平均值
    # ob.onlineCount(20,2) #t运行时间，t1是间隔多长统计一次

    ob.closeConnection()

